package com.swordget.service.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.swordget.commons.FileHandlerUtil;
import com.swordget.conf.DruidConfig;
import com.swordget.conf.UserProperties;
import com.swordget.dao.TableDefinitionDao;
import com.swordget.dao.TableDefinitionV2Dao;
import com.swordget.dao.TableManagerDao;
import com.swordget.entity.ColumnDesign;
import com.swordget.entity.TableDesign;
import com.swordget.entity.vo.SimplePageRequestVo;
import com.swordget.entity.vo.SimplePageResponseVo;
import com.swordget.exception.VerifyFailException;
import com.swordget.service.TableDefinitionService;
import com.swordget.service.TableDefinitionV2Service;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.util.*;

import static com.swordget.commons.SimpleFinalUtil.BRANCH;

/**
 * @Author: David
 * @Date: Created in 13:31 2020/3/27
 * @Description:
 * @Modified By:
 * @Version:
 */

@Service
@PropertySource("classpath:application.yml")
public class TableManagerServiceImp extends TableManagerTemplate {

    /**
     * 表结构管理 dao 层接口
     */
    private TableManagerDao tableManagerDao;

    /**
     * 表定义 dao 层接口
     */
    private TableDefinitionDao tableDefinitionDao;

    private TableDefinitionService tableDefinitionService;

    private FileHandlerUtil fileHandlerUtil;

    private UserProperties userProperties;

    /**
     * TableManagerServiceImp log
     */
    private final static Logger LOG = LoggerFactory.getLogger(TableManagerServiceImp.class);


    @Autowired
    private TableDefinitionV2Dao tableDefinitionV2Dao;

    @Autowired
    private TableDefinitionV2Service tableDefinitionV2Service;

    /**
     * 非法关键字集
     * 可以放入配置文件中
     */
    private final static List<String> unKeywords =
            Arrays.asList("DELETE", "UPDATE", "INSERT", "TRUNCATE", "DROP",
                    "ALTER", "CREATE");

    @Autowired
    public TableManagerServiceImp(TableManagerDao tableManagerDao, TableDefinitionDao tableDefinitionDao, TableDefinitionService tableDefinitionService, FileHandlerUtil fileHandlerUtil, UserProperties userProperties) {
        this.tableManagerDao = tableManagerDao;
        this.tableDefinitionDao = tableDefinitionDao;
        this.tableDefinitionService = tableDefinitionService;
        this.fileHandlerUtil = fileHandlerUtil;
        this.userProperties = userProperties;
    }

    @Autowired


    // @Value("${spring.datasource.username}")
    // private String username;

    // @Value("${spring.datasource.tablespace}")
    //private String tablespace;

    @Resource
    private DruidDataSource druidDataSource;

    /**
     * 判断数据库中是否存在表
     *
     * @param tableNameUp
     */
    @Override
    void dataTableExists(String tableNameUp) {
        String username = druidDataSource.getUsername();
        if (tableManagerDao.dataTableExists(tableNameUp, username)) {
            throw new VerifyFailException("校验失败-数据库中已经存在该表");
        }
    }

    /**
     * 执行创建表操作
     *
     * @param tableDesign   表结构
     * @param columnDesigns 字段结构
     * @param tableSpace    表空间
     * @param loginId       操作用户
     * @return
     */
    @Override
    boolean executeMake(TableDesign tableDesign, List<ColumnDesign> columnDesigns, String tableSpace, String loginId) {
        String username = druidDataSource.getUsername();
        ColumnDesign serial=tableDefinitionV2Dao.getSerialCol();
        List<ColumnDesign> list =tableDefinitionV2Dao.getCommonCols();
        columnDesigns.add(serial);
        columnDesigns.addAll(list);
        //判断是否有重复列
        String repeatCols=tableDefinitionV2Service.hasRepeatCols(columnDesigns);
        if(StringUtils.isNotBlank(repeatCols)){
            throw new RuntimeException("重复列:"+repeatCols);
        }
        //调用创建表方法
        try {
            TableDesign tmpDesign = new TableDesign();
            tmpDesign.setTableEN(tableDesign.getTableEN());
            tmpDesign.setTableCN(tableDesign.getTableCN());
            boolean isTmpMake = tableManagerDao.createTable(tmpDesign, columnDesigns, serial, tableSpace, username, loginId);
            //创建临时表
            if (!isTmpMake) {
                throw new RuntimeException("创建数据表失败!");
            }

            //初始化
            boolean isInit = tableManagerDao.initTable(tableDesign, columnDesigns, loginId, username);
            if (!isInit) {
                throw new RuntimeException("初始化失败!");
            }
            return true;
        } catch (Exception e) {
            LOG.error("程序执行过程中出现异常", e);
            tableManagerDao.dropTable(tableDesign.getTableEN());
        }
        return false;
    }

    /**
     * 获取用户表空间
     *
     * @return
     */
    @Override
    protected String getTableSpace() {
        //没有DBA权限手动设置表空间
        //String tableSpace =  tableManagerDao.getTableSpace(username);
        //return tableSpace;
        return DruidConfig.tableSpace;
    }

    @Override
    public boolean editTable(TableDesign tableDesign, String loginId) throws SQLException {
        return tableManagerDao.editTable(tableDesign, loginId);
    }

    /**
     * 删除用户表
     *
     * @param design 表结构
     * @return
     */
    @Override
    public boolean dropTable(TableDesign design) {
        String tableName = design.getTableEN().toUpperCase();
        String tableId = design.getTableId();
        //drop 首先判断是否在存在这个表
        boolean isExists = tableDefinitionDao.isExists(tableName, tableId);
        if (!isExists) {
            throw new VerifyFailException("该表不存在或不是通过系统创建!");
        }
        //判断表中是否有数据
        boolean existsData = tableManagerDao.existsData(tableName);
        if (existsData) {
            throw new VerifyFailException("操作的数据表中存在数据不允许操作!");
        }
        boolean isDrop = tableManagerDao.dropTable(tableName);
        if (isDrop) {
            //清除系统中与这个表相关联的信息
            boolean isClear = tableDefinitionDao.clearData(tableName, tableId);
            //throw new VerifyFailException("无法清除系统数据!");
        }
        return isDrop;
    }


    @Override
    public SimplePageResponseVo getTableDesign(SimplePageRequestVo pageVo) {
        int rows = pageVo.getRows();
        int page = pageVo.getPage();
        String tableEn = pageVo.getTableEn();
        String tableCn = pageVo.getTableCn();

        List<TableDesign> data = tableManagerDao.findTableDesignByPage(rows, page, tableEn, tableCn);
        int total = tableManagerDao.findTotal();
        return SimplePageResponseVo.builder()
                .total(total)
                .rows(data)
                .build();
    }

    @Override
    public SimplePageResponseVo getColumnDesign(SimplePageRequestVo pageVo) {
        String tableId = pageVo.getTableId();
        boolean showHide = pageVo.isShowHide();
        boolean showBusiness = pageVo.isShowBusiness();
        List<ColumnDesign> data = tableDefinitionDao.getColumnDesigns(tableId, showHide, showBusiness);
        return SimplePageResponseVo.builder()
                .total(data.size())
                .rows(data)
                .build();
    }

    @Override
    public boolean uploadCheckFile(MultipartFile multipartFile, String tableId, String loginId) throws IOException {
        String tableName = tableDefinitionService.getTableName(loginId, tableId);
        //读取上传的文件，读取其中的SQL语句
        StringBuilder builder = new StringBuilder();
        try (BufferedInputStream inputStream = new BufferedInputStream(multipartFile.getInputStream())) {
            byte[] bytes = new byte[1024];
            int readNum = 0;
            while ((readNum = inputStream.read(bytes)) > 0) {
                builder.append(new String(bytes, 0, readNum, StandardCharsets.UTF_8));
            }
        } catch (IOException e) {
            LOG.error("程序执行过程中出现异常", e);
        }
        String checkSQL = builder.toString().toUpperCase();
        if (checkSQL.indexOf(tableName) < 0) {
            throw new IllegalStateException("语句中存在非法表名!");
        }
        if (checkSQL.split(BRANCH).length > 1) {
            throw new IllegalStateException("为了保证更好的性能，请您使用单条校验");
        }
        String checkTmp = checkSQL.replaceAll(BRANCH, "");
        //上传的文件是否包含，DELETE,UPDATE,INSERT,TRUNCATE,DROP,ALTER等关键字,
        //防止不法分子通过此结构改动数据库结构
        Optional<String> optional = unKeywords.stream()
                .filter(t -> checkTmp.indexOf(t) > 0)
                .findFirst();
        if (optional.isPresent()) {
            throw new IllegalArgumentException("语句中存在非法关键字!");
        }

        //数据库执行下做校验
        boolean isCheck = tableManagerDao.check(checkSQL);
        if (!isCheck) {
            throw new VerifyFailException("语句验证不通过,请检查您的语句");
        }
        //将文件写入到本地
        String savePath = userProperties.getCheckFilePath();
        File saveMkdir = new File(savePath);
        if (!saveMkdir.exists()) {
            saveMkdir.mkdirs();
        }
        //每次上传不删除原有的，原有的做备份，便于排查问题
        long saveTime = System.currentTimeMillis();
        File saveFile = new File(saveMkdir.getAbsolutePath() + File.separator + tableName
                + "CheckSQL" + saveTime + ".SQL");
        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(saveFile), "UTF-8"))) {
            writer.write(checkTmp);
        }
        if (!saveFile.setReadOnly()) {
            LOG.error("无法设置文件未只读文件!");
        }
        long lastModified = saveFile.lastModified();
        String filePath = saveFile.getAbsolutePath();
        filePath = filePath.replaceAll("\\\\\\\\", "/");
        filePath = filePath.replaceAll("\\\\", "/");
        boolean isModify = tableManagerDao.updateCheckFile(filePath, lastModified, tableId);
        return isModify;
    }

    @Override
    public TableDesign flinOne(String tableId) {
        return tableManagerDao.findOne(tableId);
    }
}

